﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading;
using System.Threading.Tasks;
using System.Xml;
using Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling;

namespace Nardax.Data
{
    /// <summary> 
    /// An abstraction of the functions of a SQL Database. 
    /// </summary> 
    public class SqlDatabase : Database<SqlCommand, SqlTransaction, SqlConnection, SqlDataAdapter>, ISqlDatabase
    {
        private readonly int _batchSize;
 
        /// <summary> 
        /// Instanciate an instnace of SQL Database using the default Database Connection Policy from configuration file. 
        /// </summary> 
        /// <remarks>The <see cref="RetryPolicy.NoRetry"/> will be used when making connection to the database.</remarks>
        public SqlDatabase()
            : this(DatabaseConnectionPolicy.Default)
        {
        }

        /// <summary>
        /// Instanciate an instance of SQL Database using the connection policy provided.
        /// </summary>
        /// <param name="databaseConnectionPolicy">The specific connection policy that should be used.</param>
        /// <remarks>The <see cref="RetryPolicy.NoRetry"/> will be used when making connection to the database.</remarks>
        public SqlDatabase(DatabaseConnectionPolicy databaseConnectionPolicy)
            : this(databaseConnectionPolicy, RetryPolicy.NoRetry)
        {
        }

        /// <summary>
        /// Instanciate an instance of SQL Database using the retry policy provided.
        /// </summary>
        /// <param name="retryPolicy">The specific retry policy that should be used when making connections to the database.</param>
        /// <remarks>The default <see cref="DatabaseConnectionPolicy"/> will be used</remarks>
        public SqlDatabase(RetryPolicy retryPolicy)
            : this(DatabaseConnectionPolicy.Default, retryPolicy)
        {
        }

        /// <summary> 
        /// Instanciate a SQL database using the connection string in the config file that can be located 
        /// by the name passed in. 
        /// </summary> 
        /// <param name="databaseConnectionPolicy">The specific connection policy that should be used.</param>
        /// <param name="retryPolicy">The specific retry policy that should be used when making connections to the database.</param>
        public SqlDatabase(DatabaseConnectionPolicy databaseConnectionPolicy, RetryPolicy retryPolicy)
            : base(databaseConnectionPolicy, retryPolicy)
        {
            _batchSize = databaseConnectionPolicy.DefaultBatchSize;
        }

        /// <summary>
        /// Opens a connection to the database and returns the results of the command as an XmlReader object.
        /// </summary>
        /// <param name="command">The command to be executed.</param> 
        /// <param name="connection">The connection that will be used to execute the command on.</param>
        /// <returns>The XmlReader object that is created from the results of the command.</returns>
        /// <remarks>Working with the XmlReader requires an open connection but does not supply a way to close the
        /// connection when the reader is closed. So the caller is responsable for creating an open connection and then
        /// closing and displosing of the connection once the work with the XmlReader is complete.</remarks>
        public XmlReader ExecuteXmlReader(SqlCommand command, SqlConnection connection)
        {
            if (command == null)
            {
                throw new ArgumentNullException("command");
            }

            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }

            command.Connection = connection;
            return RetryPolicy.ExecuteAction(() => command.ExecuteXmlReader());
        }

        /// <summary>
        /// An asynchronous version of <see cref="ExecuteXmlReader"/>. Opens a connection to the database and returns 
        /// the results of the command as an XmlReader object.
        /// </summary>
        /// <param name="command">The command to be executed.</param> 
        /// <param name="connection">The connection that will be used to execute the command on.</param>
        /// <returns>The XmlReader object that is created from the results of the command.</returns>
        /// <remarks>Working with the XmlReader requires an open connection but does not supply a way to close the
        /// connection when the reader is closed. So the caller is responsable for creating an open connection and then
        /// closing and displosing of the connection once the work with the XmlReader is complete.</remarks>
        public async Task<XmlReader> ExecuteXmlReaderAsync(SqlCommand command, SqlConnection connection)
        {
            return await ExecuteXmlReaderAsync(command, connection, CancellationToken.None);
        }

        /// <summary>
        /// An asynchronous version of <see cref="ExecuteXmlReader"/>. Opens a connection to the database and returns the 
        /// results of the command as an XmlReader object.
        /// </summary>
        /// <param name="command">The command to be executed.</param> 
        /// <param name="connection">The connection that will be used to execute the command on.</param>
        /// <param name="cancellationToken">The cancellation token that can be used to cancel the task.</param>
        /// <returns>The XmlReader object that is created from the results of the command.</returns>
        /// <remarks>Working with the XmlReader requires an open connection but does not supply a way to close the
        /// connection when the reader is closed. So the caller is responsable for creating an open connection and then
        /// closing and displosing of the connection once the work with the XmlReader is complete.</remarks>
        public async Task<XmlReader> ExecuteXmlReaderAsync(SqlCommand command, SqlConnection connection, CancellationToken cancellationToken)
        {
            if (command == null)
            {
                throw new ArgumentNullException("command");
            }

            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }

            command.Connection = connection;
            return await RetryPolicy.ExecuteAsync(async () => await command.ExecuteXmlReaderAsync(cancellationToken), cancellationToken);
        }

        /// <summary>
        /// Uses the SqlBulkCopy class to move all of the data from the table provided to the table that is
        /// named in the destinationTableName parameter.
        /// </summary>
        /// <param name="destinationTableName">Name of the destination table in the database.</param>
        /// <param name="table">The table with the data to be copied to the database.</param>
        public void WriteToServer(string destinationTableName, DataTable table)
        {
            WriteToServer(destinationTableName, table, _batchSize);
        }

        /// <summary>
        /// An asynchronous version of <see cref="WriteToServer"/>. Uses the SqlBulkCopy class to move all of the 
        /// data from the table provided to the table that is named in the destinationTableName parameter.
        /// </summary>
        /// <param name="destinationTableName">Name of the destination table in the database.</param>
        /// <param name="table">The table with the data to be copied to the database.</param>
        public async Task WriteToServerAsync(string destinationTableName, DataTable table)
        {
            await WriteToServerAsync(destinationTableName, table, _batchSize, CancellationToken.None);
        }

        /// <summary>
        /// An asynchronous version of <see cref="WriteToServer"/>. Uses the SqlBulkCopy class to move all of the 
        /// data from the table provided to the table that is named in the destinationTableName parameter.
        /// </summary>
        /// <param name="destinationTableName">Name of the destination table in the database.</param>
        /// <param name="table">The table with the data to be copied to the database.</param>
        /// <param name="cancellationToken">The cancellation token that can be used to cancel the task.</param>
        public async Task WriteToServerAsync(string destinationTableName, DataTable table, CancellationToken cancellationToken)
        {
            await WriteToServerAsync(destinationTableName, table, _batchSize, cancellationToken);
        }

        /// <summary>
        /// Uses the SqlBulkCopy class to move all of the data from the table provided to the table that is
        /// named in the destinationTableName parameter.
        /// </summary>
        /// <param name="destinationTableName">Name of the destination table in the database.</param>
        /// <param name="table">The table with the data to be copied to the database.</param>
        /// <param name="batchSize">Number of rows in each batch. At the end of each batch, the rows
        /// in the batch are sent to the server.</param>
        public void WriteToServer(string destinationTableName, DataTable table, int batchSize)
        {
            using (var connection = CreateOpenConnection())
            {
                // calling dispose on SqlBulkCopy does not result in a call to dispose on connection
                using (var bulkCopy = new SqlBulkCopy(connection))
                {
                    bulkCopy.DestinationTableName = destinationTableName;
                    bulkCopy.BatchSize = batchSize;
                    RetryPolicy.ExecuteAction(() => bulkCopy.WriteToServer(table));
                }
            }
        }

        /// <summary>
        /// An asynchronous version of <see cref="WriteToServer"/>. Uses the SqlBulkCopy class to move all of the data from the 
        /// table provided to the table that is named in the destinationTableName parameter.
        /// </summary>
        /// <param name="destinationTableName">Name of the destination table in the database.</param>
        /// <param name="table">The table with the data to be copied to the database.</param>
        /// <param name="batchSize">Number of rows in each batch. At the end of each batch, the rows
        /// in the batch are sent to the server.</param>
        public async Task WriteToServerAsync(string destinationTableName, DataTable table, int batchSize)
        {
            await WriteToServerAsync(destinationTableName, table, batchSize, CancellationToken.None);
        }

        /// <summary>
        /// An asynchronous version of <see cref="WriteToServer"/>. Uses the SqlBulkCopy class to move all of the data from the 
        /// table provided to the table that is named in the destinationTableName parameter.
        /// </summary>
        /// <param name="destinationTableName">Name of the destination table in the database.</param>
        /// <param name="table">The table with the data to be copied to the database.</param>
        /// <param name="batchSize">Number of rows in each batch. At the end of each batch, the rows
        /// in the batch are sent to the server.</param>
        /// <param name="cancellationToken">The cancellation token that can be used to cancel the task.</param>
        public async Task WriteToServerAsync(string destinationTableName, DataTable table, int batchSize, CancellationToken cancellationToken)
        {
            using (var connection = await CreateOpenConnectionAsync(cancellationToken))
            {
                // calling dispose on SqlBulkCopy does not result in a call to dispose on connection
                using (var bulkCopy = new SqlBulkCopy(connection))
                {
                    bulkCopy.DestinationTableName = destinationTableName;
                    bulkCopy.BatchSize = batchSize;
                    await RetryPolicy.ExecuteAsync(async () => await bulkCopy.WriteToServerAsync(table, cancellationToken), cancellationToken);
                }
            }

        }
    }
}